﻿using System;
using System.Data;
using System.Collections.Generic;
using Maticsoft.Common;
using CustomsSystem.Model;
namespace CustomsSystem.BLL
{
    /// <summary>
    /// Bill
    /// </summary>
    public partial class BillManage
    {
        private readonly CustomsSystem.DAL.BillService dal = new CustomsSystem.DAL.BillService();
        public BillManage()
        { }
        #region  BasicMethod
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int billID)
        {
            return dal.Exists(billID);
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool ExistsSheetid(int sheetid)
        {
            return dal.ExistsSheetid(sheetid);
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string feetypeid)
        {
            return dal.Exists(feetypeid);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(CustomsSystem.Model.Bill model)
        {
            return dal.Add(model);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CustomsSystem.Model.Bill model)
        {
            return dal.Update(model);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdatePayedID(CustomsSystem.Model.Bill model)
        {
            return dal.UpdatePayedID(model);
        }

        /// <summary>
        /// 根据sheetID和应收应付的类型，更新T_bill表中的payedID
        /// </summary>
        public void updatePayedID(int sheetID, int billTypeID)
        {
            dal.updatePayedID(sheetID,billTypeID);
        }
        
        /// <summary>
        /// 填入审核人
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateAudit(CustomsSystem.Model.Bill model)
        {
            return dal.UpdateAudit(model);
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int sheetID, int billTypeID)
        {

            return dal.Delete(sheetID, billTypeID);
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteList(string billIDlist)
        {
            return dal.DeleteList(billIDlist);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.Bill GetModel(int billID)
        {

            return dal.GetModel(billID);
        }

        ///// <summary>
        ///// 执行一条计算查询结果语句，返回查询结果（object）。
        ///// </summary>
        //public object GetSingle(string SQLString)
        //{
        //    return dal.GetSingle(SQLString);
        //}

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        public DataSet getBillID(int sheetID, int billTypeID)
        {
            return dal.getBillID(sheetID,billTypeID);
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果payedID（object）。
        /// </summary>
        public object GetPayedID(string SQLString)
        {
            return dal.GetPayedID(SQLString);
        }

        /// <summary>
        /// 根据sheetID和应收应付类型，查询该sheetID是否有费用数据。
        /// </summary>
        public object GetSfeeAmount(int sheetID, int type)
        {
            return dal.GetSfeeAmount(sheetID,type);
        }

        /// <summary>
        /// 执行一条查询语句，返回查询结果billTypeId。
        /// </summary>
        public DataSet  GetbillTypeId(int sheetid)
        {
            return dal.GetbillTypeId (sheetid );
        }

        /// <summary>
        /// 执行一条查询语句，返回查询结果billTypeId。
        /// </summary>
        public DataSet GetbillTypeId2(int sheetid)
        {
            return dal.GetbillTypeId2(sheetid);
        }

        /// <summary>
        ///  根据sheetID和应收应付类型，查询该委托单是否已审核。
        /// </summary>
        public object GetAuditEmpID(int sheetID, int type)
        {
            return dal.GetAuditEmpID(sheetID, type);
        }


        /*
                /// <summary>
                /// 得到一个对象实体，从缓存中
                /// </summary>
                public CustomsSystem.Model.Bill GetModelByCache(int billID)
                {
			
                    string CacheKey = "BillModel-" + billID;
                    object objModel = Maticsoft.Common.DataCache.GetCache(CacheKey);
                    if (objModel == null)
                    {
                        try
                        {
                            objModel = dal.GetModel(billID);
                            if (objModel != null)
                            {
                                int ModelCache = Maticsoft.Common.ConfigHelper.GetConfigInt("ModelCache");
                                Maticsoft.Common.DataCache.SetCache(CacheKey, objModel, DateTime.Now.AddMinutes(ModelCache), TimeSpan.Zero);
                            }
                        }
                        catch{}
                    }
                    return (CustomsSystem.Model.Bill)objModel;
                }
                */
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            return dal.GetList(strWhere);
        }
        /// <summary>
        /// 获取票结列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetTicketList(string strWhere)
        {
            DataSet ds = new DataSet();
            string sql = "";
            string[] sheetid = strWhere.Split(',');
            foreach (string i in sheetid)
            {
                DataTable dt2 = new DataTable();
                sql = " a.sheetid= " + i;
                dt2 = dal.GetTicketList(sql).Tables[0].Copy();
                dt2.TableName=i;
                ds.Tables.Add(dt2);
            }
            return ds;
        }
        /// <summary>
        /// 获取费用列表
        /// </summary>
        public DataSet GetFeeList(string strWhere)
        {
            return dal.GetFeeList(strWhere);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(int sheetID, int billTypeID)
        {
            return dal.GetList(sheetID, billTypeID);
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            return dal.GetList(Top, strWhere, filedOrder);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<CustomsSystem.Model.Bill> GetModelList(string strWhere)
        {
            DataSet ds = dal.GetList(strWhere);
            return DataTableToList(ds.Tables[0]);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<CustomsSystem.Model.Bill> DataTableToList(DataTable dt)
        {
            List<CustomsSystem.Model.Bill> modelList = new List<CustomsSystem.Model.Bill>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                CustomsSystem.Model.Bill model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = dal.DataRowToModel(dt.Rows[n]);
                    if (model != null)
                    {
                        modelList.Add(model);
                    }
                }
            }
            return modelList;
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetAllList()
        {
            return GetList("");
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            return dal.GetRecordCount(strWhere);
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            return dal.GetListByPage(strWhere, orderby, startIndex, endIndex);
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        //public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        //{
        //return dal.GetList(PageSize,PageIndex,strWhere);
        //}
        /// <summary>
        /// 获取月结表
        /// </summary>
        /// <param name="strFeetype">内外部费用</param>
        /// <param name="agencyDate">日期</param>
        /// <param name="cid">公司编号</param>
        /// <param name="strDate">日期的排序</param>
        /// <returns></returns>
        public DataTable GetPivot(string strFeetype, string agencyDate, string cid,string strDate)
        {
            //return dal.GetPivot (strWhere,strDate,strFeetype,agencyDate ,cid );
            DataTable dt = new DataTable();//旧表
            DataTable newdt = new DataTable();//行列转换新表
            dt = dal.GetPivot(strFeetype, agencyDate, cid,strDate).Tables[0];//获取数据
            if (dt.Rows.Count > 0)
            {
                string feename = "";//费用名称
                decimal Sum = 0;//费用合计
                foreach (DataColumn dc in dt.Columns)//获取列名
                {
                    if (dc.ColumnName == "feeName")
                    { break; }
                    newdt.Columns.Add(dc.ColumnName, dc.DataType);
                }
                foreach (DataRow dr in dt.Rows)//获取费用名
                {
                    if (feename.IndexOf(dr["feeName"].ToString()) < 0)//去重复
                    {
                        feename += dr["feeName"].ToString() + ",";
                    }
                }
                feename = feename.TrimEnd(",".ToCharArray());
                string[] arryFeename = feename.Split(',');
                foreach (string i in arryFeename)//增加费用列
                {
                    newdt.Columns.Add(i, typeof(Decimal));
                }
                newdt.Columns.Add("合计", typeof(Decimal));//增加合计列
                //倒入数据
                string id = "";
                for (int row = 0; row < dt.Rows.Count; )
                {
                    DataRow drow = newdt.NewRow();
                    id = dt.Rows[row][0].ToString();//记录委托单号
                    for (int i = 0; i < dt.Columns.Count; i++)//添加数据
                    {
                        if (dt.Columns[i].ColumnName != "feeName")
                        {
                            drow[i] = dt.Rows[row][i];//费用外的列赋值
                        }
                        else
                        {
                            for (int x = row; x < dt.Rows.Count; x++)//取费用的值
                            {
                                if (id == dt.Rows[x][0].ToString())
                                {
                                    decimal temp;
                                    if (dt.Rows[x]["sfeeAmount"].ToString() != "")
                                    {
                                        temp = Convert.ToDecimal(dt.Rows[x]["sfeeAmount"].ToString());//去费用的值
                                        drow[dt.Rows[x]["feeName"].ToString()] = temp;
                                        Sum = Sum + temp;//费用的合计
                                        row++;

                                    }
                                }
                                else
                                {
                                    break;
                                }
                            }
                            drow["合计"] = Sum;
                            Sum = 0;
                            newdt.Rows.Add(drow);//加行
                            break;
                        }
                    }
                }
            }
            //DataSet ds = new DataSet();
            //ds.Tables.Add(newdt);
            return newdt;


        }
        /// <summary>
        /// 获取月结表（壳牌）
        /// </summary>
        /// <param name="strFeetype">内外部费用</param>
        /// <param name="agencyDate">日期</param>
        /// <param name="cid">公司编号</param>
        /// <param name="strDate">日期的排序</param>
        /// <returns></returns>
        public DataTable GetPivot_QiaoPai(string strFeetype, string agencyDate, string cid, string strDate)
        {
            //return dal.GetPivot (strWhere,strDate,strFeetype,agencyDate ,cid );
            DataTable dt = new DataTable();//旧表
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            DataTable dt3 = new DataTable();
            DataTable newdt = new DataTable();//行列转换新表
            int a = 0;
            int b = 0;
            dt = dal.GetPivot_QiaoPai(strFeetype, agencyDate, cid, strDate).Tables[0];//获取数据
            dt1 = dal.GetPivot_QiaoPai(strFeetype, agencyDate, cid, strDate).Tables[1];
            dt2 = dal.GetPivot_QiaoPai(strFeetype, agencyDate, cid, strDate).Tables[2];
            dt3 = dal.GetPivot_QiaoPai(strFeetype, agencyDate, cid, strDate).Tables[3];
            if (dt.Rows.Count > 0)
            {
                string feename = "";//费用名称
                decimal Sum = 0;//费用合计
                foreach (DataColumn dc in dt.Columns)//获取列名
                {
                    if (dc.ColumnName == "feeName")
                    { break; }
                    newdt.Columns.Add(dc.ColumnName, dc.DataType);
                }
                //foreach (DataRow dr in dt1.Rows)//获取费用名
                //{
                //    if (feename.IndexOf(dr["feeName"].ToString()) < 0)//去重复
                //    {
                //        feename += dr["feeName"].ToString() + ",";
                //    }
                //}
                //feename = feename.TrimEnd(",".ToCharArray());
                //string[] arryFeename = feename.Split(',');
                //foreach (string i in arryFeename)//增加费用列
                //{
                //    newdt.Columns.Add(i, typeof(Decimal));
                //}
                newdt.Columns.Add("运费", typeof(Decimal));//增加运费列
                newdt.Columns.Add("代理费用", typeof(Decimal));//增加代理费列
                foreach (DataColumn dc in dt2.Columns)
                {
                    if (dc.ColumnName != "sheetID")
                    {
                        newdt.Columns.Add(dc.ColumnName, dc.DataType);
                    }
                }
                foreach (DataColumn dc in dt3.Columns)
                {
                    if (dc.ColumnName != "sheetID")
                    {
                        newdt.Columns.Add(dc.ColumnName, dc.DataType);
                    }
                }
                newdt.Columns.Add("加收6%税款", typeof(Decimal));//增加税费列
                newdt.Columns.Add("驻厂人员工资", typeof(Decimal));
                newdt.Columns.Add("合计", typeof(Decimal));//增加合计列
                newdt.Columns.Add("滞期费", typeof(Decimal));
                newdt.Columns.Add("备注", typeof(Decimal));
                //倒入数据
                string id = "";
                for (int row = 0; row < dt.Rows.Count;row++ )
                {
                    DataRow drow = newdt.NewRow();
                    id = dt.Rows[row][0].ToString();//记录委托单号
                    for (int i = 0; i < dt.Columns.Count+4; i++)//添加数据
                    {
                        //if (dt.Columns[i].ColumnName != "feeName")
                        //{
                            if (i+1 <dt.Columns.Count)
                            {
                                drow[i] = dt.Rows[row][i];//费用外的列赋值
                                
                            }
                            else
                            {
                                if (dt1.Rows.Count > 0)
                                {
                                    for (int x = 0; x < dt1.Rows.Count; x++)//取费用的值
                                    {
                                        if (id == dt1.Rows[x][0].ToString())
                                        {
                                            decimal temp;
                                            if (dt1.Rows[x]["sfeeAmount"].ToString() != "")
                                            {
                                                temp = Convert.ToDecimal(dt1.Rows[x]["sfeeAmount"].ToString());//取费用的值
                                                drow[dt1.Rows[x]["feeName"].ToString()] = temp;
                                                Sum = Sum + temp;//费用的合计
                                                //row++;

                                            }
                                        }

                                    }
                                }

                                if (dt2.Rows.Count > 0)
                                {
                                    for (int c = 0; c < dt2.Rows.Count; c++)
                                    {
                                        if (id==dt2.Rows[c][0].ToString())
                                        {
                                            drow[i + 3] = dt2.Rows[c][1];
                                            a++;
                                            Sum += Convert.ToDecimal(dt2.Rows[c][1].ToString());
                                        }
                                    }

                                }
                                if (dt3.Rows.Count > 0)
                                {
                                    for (int c = 0; c < dt3.Rows.Count; c++)
                                    {
                                        if (id==dt3.Rows[c][0] .ToString())
                                        {
                                            drow[i + 4] = dt3.Rows[c][1];
                                            drow[i + 5] = (Convert.ToDouble(dt3.Rows[c][1]) * 0.06).ToString();
                                            Sum += decimal.Parse((Convert.ToDouble(dt3.Rows[c][1]) * 0.06).ToString());
                                            Sum += Convert.ToDecimal(dt3.Rows[c][1].ToString());
                                            b++;
                                        }
                                    }
                                    break;
                                }
                                break;
                            }
                            
                       // }
                        //else
                        //{
                        //    for (int x = row; x < dt.Rows.Count; x++)//取费用的值
                        //    {
                        //        if (id == dt.Rows[x][0].ToString())
                        //        {
                        //            decimal temp;
                        //            if (dt.Rows[x]["sfeeAmount"].ToString() != "")
                        //            {
                        //                temp = Convert.ToDecimal(dt.Rows[x]["sfeeAmount"].ToString());//去费用的值
                        //                drow[dt.Rows[x]["feeName"].ToString()] = temp;
                        //                Sum = Sum + temp;//费用的合计
                        //                row++;

                        //            }
                        //        }
                        //        else
                        //        {
                        //            //break;
                        //        }
                         //}
                            //drow["合计"] = Sum;
                            //Sum = 0;
                            //newdt.Rows.Add(drow);//加行
                            //break;
                        //}
                    }
                    drow["合计"] = Sum;
                    Sum = 0;
                    newdt.Rows.Add(drow);//加行
                }
            }
            //DataSet ds = new DataSet();
            //ds.Tables.Add(newdt);
            return newdt;


        }
        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

